from common.connect_db import MyDB

my_db = MyDB()


class DataBaseMember:

    @staticmethod
    def filter_life_member_by_mobile(mobile):
        """
        通过手机号获取用户的所有信息
        :param mobile: 手机号
        :return: 返回对象
        """
        sql = "select * from life_member where username = '%s'" % mobile
        result = my_db.execute_select('member', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("查询出错！")

    @staticmethod
    def filter_life_member_address_by_member_id(member_id):
        """
        通过用户ID获得用户所有地址
        :param member_id: 手机号
        :return: 返回对象
        """
        sql = "select * from life_member_address where member_id = '%s' and deleted_at is null " % member_id
        result = my_db.execute_select('member', sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("该用户没有添加地址！！")

    @staticmethod
    def get_id(username):
        """
        通过手机号获取到会员id
        :param username:
        :return:
        """
        sql = "select id from life_member " \
              " ""where username='%s' and status=1" % username
        id = my_db.execute_select('member', sql)
        if id[0]:
            return id[0][0]
        else:
            print("数据出错了，请检查是否存在该会员")

    @staticmethod
    def get_addressid(memberid):
        """
        查询会员地址id
        :return:
        """
        sql = "select id  from life_member_address " \
              " ""where member_id='%s'" % memberid
        addressid = my_db.execute_select('member', sql)
        if addressid[0]:
            return addressid[0][0]
        else:
            print("数据出错了，请检查是否存在该会员地址")

    @staticmethod
    def get_random_user_list(member_id_one, member_id_two):
        """
        随机取得ID大于某个值的用户列表
        :return: 大于某个ID的所有用户信息
        """
        sql = "select * from life_member_infos where member_id>='%s' and member_id<='%s'" % (
            member_id_one, member_id_two)
        result = my_db.execute_select('member', sql)
        if len(result) >= 1:
            return result
        else:
            raise ValueError("没有查询到合适的数据")

    @staticmethod
    def get_member_info_by_member_id(member_id):
        """
        通过用户id取得life_member_info表的所有信息
        :return: 大于某个ID的所有用户信息
        """
        sql = "select * from life_member_infos where member_id='%s'" % member_id
        result = my_db.execute_select('member', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("没有查询到合适的数据")

    @staticmethod
    def filter_life_member_by_member_id(member_id):
        """
        通过用户id取得life_member表的所有信息
        :return:
        """
        sql = "select * from life_member where id='%s'" % member_id
        result = my_db.execute_select('member', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("没有查询到合适的数据")

    @staticmethod
    def get_life_member_coin_tribute_by_info(member_id, level, user_type, recommend_id):
        """
        获得用户或者商家的进贡信息
        :param member_id:
        :param level:
        :param user_type: 0：会员 1：商家
        :param recommend_id
        :return:
        """
        sql = "select * from life_member_coin_tribute " \
              "where member_id = %s and level = '%s' and type = '%s' and recommend_id = '%s' " \
              "" % (member_id, level, user_type, recommend_id)
        result = my_db.execute_select('member', sql)
        if len(result) == 1:
            return result[0]
        elif len(result) == 0:
            from collections import namedtuple
            select_info = namedtuple(
                "select_info", ["id", "member_id", "type", "level", 'recommend_id',
                                "month_coin", 'total_coin', 'create_time', 'update_time'])
            temp = (0, member_id, user_type, level, recommend_id, 0, 0, "", "")
            demo_json = select_info(*temp)
            return demo_json
        else:
            raise ValueError("查找【common_coin_tribute】数据异常！存在多条相同记录！")

    @staticmethod
    def get_all_user():
        """
        无条件获取所有用户
        :return:
        """
        sql = "select * from life_member where traders_password <> '' and traders_password is not null limit 5000"
        result = my_db.execute_select('member', sql, False)
        if len(result) > 0:
            return result
        elif len(result) == 0:
            raise ValueError("没有数据")

    @staticmethod
    def get_all_user_mobile():
        """
        无条件获取所有用户
        :return:
        """
        sql = "select username from life_member"
        result = my_db.execute_select('member', sql, False)
        if len(result) > 0:
            return result
        elif len(result) == 0:
            raise ValueError("没有数据")

    @staticmethod
    def get_id_and_balance(user_list):

        sql = "select id,account_id,coin_id from life_member where username in " + str(user_list)
        print(sql)
        result = my_db.execute_select('member', sql, False)
        if len(result) > 0:
            return result
        elif len(result) == 0:
            raise ValueError("没有数据")

    @staticmethod
    def filter_offshop_store():
        sql = "select a.id,a.member_id,a.shop_name,a.service_rate " \
              "from offshop.life_offline_shop_shops as a " \
              "LEFT JOIN member.life_member as b on a.member_id = b.id " \
              "where a.top_category <>0 and a.second_category<>0 and a.id<4324013 and a.status=1 " \
              "limit 500 "
        result = my_db.execute_select('member', sql, False)
        if len(result) > 0:
            return result
        elif len(result) == 0:
            raise ValueError("没有数据")


if __name__ == '__main__':
    id = DataBaseMember.get_id("13622220000")
    print(id)
